#include"db.h"

// 查看成绩（支持管理员按学号查询）
void view_grades(UserDatabase *db, int user_index) 
{
    char id[20];
    int is_admin = db->users[user_index].is_admin;

     if (is_admin) 
     {
        printf("\n请输入要查询的学生学号: ");
        scanf("%19s", id);
        clear_input_buffer();
    } else {
        strcpy(id, db->users[user_index].id);
    }
    
    const char *sql = "SELECT * FROM grades WHERE id = ?;";
    sqlite3_stmt *stmt;
    
    int rc = sqlite3_prepare_v2(db->db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) 
    {
        fprintf(stderr, "准备查询失败: %s\n", sqlite3_errmsg(db->db));
        return;
    }
    
    sqlite3_bind_text(stmt, 1, id, -1, SQLITE_STATIC);
    
    rc = sqlite3_step(stmt);
    if (rc == SQLITE_ROW) 
    {
        printf("\n=== 成绩信息 ===\n");
        printf("学号: %s\n", sqlite3_column_text(stmt, 0));
        printf("姓名: %s\n", sqlite3_column_text(stmt, 1));
        printf("班级: %s\n", sqlite3_column_text(stmt, 2));
        printf("语文: %.2f\n", sqlite3_column_double(stmt, 3));
        printf("数学: %.2f\n", sqlite3_column_double(stmt, 4));
        printf("英语: %.2f\n", sqlite3_column_double(stmt, 5));
        printf("物理: %.2f\n", sqlite3_column_double(stmt, 6));
        printf("化学: %.2f\n", sqlite3_column_double(stmt, 7));
        printf("生物: %.2f\n", sqlite3_column_double(stmt, 8));
        printf("总分: %.2f\n", sqlite3_column_double(stmt, 9));
    } else {
        printf("\n暂无成绩信息\n");
    }
    
    sqlite3_finalize(stmt);
}

// 更新学生成绩
/*void update_student_score(UserDatabase *db) {
    char student_id[20];
    printf("\n请输入要更新成绩的学生学号: ");
    scanf("%19s", student_id);
    clear_input_buffer();
    
    int index = -1;
    for (int i = 0; i < db->count; i++) {
        if (!db->users[i].is_admin && strcmp(db->users[i].student_id, student_id) == 0) {
            index = i;
            break;
        }
    }
    
    if (index == -1) {
        printf("错误：未找到该学生\n");
        return;
    }

    User *student = &db->users[index];
    
    printf("\n=== 更新成绩 ===\n");
    printf("学生: %s (学号: %s)\n", student->username, student->student_id);
    
    printf("当前数学成绩: %.1f\n新数学成绩: ", student->math);
    scanf("%f", &student->math);
    clear_input_buffer();
    
    printf("当前英语成绩: %.1f\n新英语成绩: ", student->english);
    scanf("%f", &student->english);
    clear_input_buffer();
    
    printf("当前科学成绩: %.1f\n新科学成绩: ", student->science);
    scanf("%f", &student->science);
    clear_input_buffer();
    
    save_users(db);
    printf("\n成绩已更新！\n");
}*/

//成绩与数据库连接
int add_grade(UserDatabase *db, Student *stu) 
{
    // 计算总成绩
    stu->total = stu->chinese + stu->math + stu->english 
               + stu->physics + stu->chemistry + stu->biology;

    // 使用参数化查询
    const char *sql = "INSERT INTO grades (id, name, class, chinese, math, english, "
                      "physics, chemistry, biology, total) "
                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
    
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db->db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) 
    {
        fprintf(stderr, "准备SQL失败: %s\n", sqlite3_errmsg(db->db));
        return -1;
    }
    
    sqlite3_bind_text(stmt, 1, stu->id, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, stu->name, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 3, stu->class, -1, SQLITE_STATIC);
    sqlite3_bind_double(stmt, 4, stu->chinese);
    sqlite3_bind_double(stmt, 5, stu->math);
    sqlite3_bind_double(stmt, 6, stu->english);
    sqlite3_bind_double(stmt, 7, stu->physics);
    sqlite3_bind_double(stmt, 8, stu->chemistry);
    sqlite3_bind_double(stmt, 9, stu->biology);
    sqlite3_bind_double(stmt, 10, stu->total);
    
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) 
    {
        fprintf(stderr, "录入失败: %s\n", sqlite3_errmsg(db->db));
        sqlite3_finalize(stmt);
        return -1;
    }
    
    sqlite3_finalize(stmt);
    return 0;
}

//录入成绩
void input_grade(UserDatabase *db) 
{
    Student stu = {0};
    char id[20];
    
    printf("\n=== 录入学生成绩 ===\n");
    printf("请输入学生学号: ");
    scanf("%19s", id);
    clear_input_buffer();
    
    // 查找学生是否存在
    int index = -1;
    for (int i = 0; i < db->count; i++) {
        if (!db->users[i].is_admin && strcmp(db->users[i].id, id) == 0) {
            index = i;
            break;
        }
    }
    
    if (index == -1) {
        printf("错误：未找到该学生\n");
        return;
    }
    
    // 填充学生基本信息
    User *user = &db->users[index];
    strcpy(stu.id, user->id);
    strcpy(stu.name, user->username);
    strcpy(stu.class, user->class_name);
    
    // 输入各科成绩
    printf("语文成绩: ");
    scanf("%f", &stu.chinese);
    clear_input_buffer();
    
    printf("数学成绩: ");
    scanf("%f", &stu.math);
    clear_input_buffer();
    
    printf("英语成绩: ");
    scanf("%f", &stu.english);
    clear_input_buffer();
    
    printf("物理成绩: ");
    scanf("%f", &stu.physics);
    clear_input_buffer();
    
    printf("化学成绩: ");
    scanf("%f", &stu.chemistry);
    clear_input_buffer();
    
    printf("生物成绩: ");
    scanf("%f", &stu.biology);
    clear_input_buffer();
    
    // 录入成绩
    if (add_grade(db, &stu) == 0) {
        printf("\n成绩录入成功！\n");
        printf("学号: %s\n姓名: %s\n总分: %.2f\n", 
               stu.id, stu.name, stu.total);
    }
}

// 获取班级列表
int get_class_list(UserDatabase *db, char classes[][30]) {
    int class_count = 0;
    
    for (int i = 0; i < db->count; i++) {
        if (!db->users[i].is_admin && db->users[i].class_name[0] != '\0') {
            int found = 0;
            for (int j = 0; j < class_count; j++) {
                if (strcmp(classes[j], db->users[i].class_name) == 0) {
                    found = 1;
                    break;
                }
            }
            if (!found) {
                strcpy(classes[class_count], db->users[i].class_name);
                class_count++;
            }
        }
    }
    
    return class_count;
}

// 分析班级成绩
void analyze_class_grades(UserDatabase *db) 
{
    char classes[50][30]; // 最多50个班级
    int class_count = get_class_list(db, classes);
    
    if (class_count == 0) 
    {
        printf("\n暂无班级数据\n");
        return;
    }
    
    // 显示班级列表
    printf("\n=== 选择班级 ===\n");
    for (int i = 0; i < class_count; i++) 
    {
        printf("%d. %s\n", i + 1, classes[i]);
    }
    
    int choice;
    printf("\n请选择班级: ");
    scanf("%d", &choice);
    clear_input_buffer();
    
    if (choice < 1 || choice > class_count) 
    {
        printf("错误：无效选择\n");
        return;
    }
    
    char selected_class[30];
    strcpy(selected_class, classes[choice - 1]);
    
    // 初始化统计信息
    ClassStats stats = {0};
    strcpy(stats.class_name, selected_class);
    
    // 设置初始值
    stats.max_total = -1;
    stats.min_total = 1000; // 假设总分不会超过1000
    stats.max_chinese = -1;
    stats.min_chinese = 100;
    stats.max_math = -1;
    stats.min_math = 100;
    stats.max_english = -1;
    stats.min_english = 100;
    stats.max_physics = -1;
    stats.min_physics = 100;
    stats.max_chemistry = -1;
    stats.min_chemistry = 100;
    stats.max_biology = -1;
    stats.min_biology = 100;
    
    // 查询该班级所有学生的成绩
    const char *sql = "SELECT grades.id, users.username, "
                     "grades.chinese, grades.math, grades.english, "
                     "grades.physics, grades.chemistry, grades.biology, grades.total "
                     "FROM grades "
                     "JOIN users ON grades.id = users.id "
                     "WHERE users.class_name = ?;";
    
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db->db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) 
    {
        fprintf(stderr, "准备查询失败: %s\n", sqlite3_errmsg(db->db));
        return;
    }
    
    sqlite3_bind_text(stmt, 1, selected_class, -1, SQLITE_STATIC);
    
    printf("\n=== %s班 成绩分布 ===\n", selected_class);
    printf("%-15s   %-15s   %-8s   %-8s   %-8s   %-8s   %-8s   %-8s   %-8s\n", 
           "学号", "姓名", "语文", "数学", "英语", "物理", "化学", "生物", "总分");
    
    while (sqlite3_step(stmt) == SQLITE_ROW) 
    {
        stats.count++;
        
        const char *id = (const char*)sqlite3_column_text(stmt, 0);
        const char *name = (const char*)sqlite3_column_text(stmt, 1);
        float chinese = sqlite3_column_double(stmt, 2);
        float math = sqlite3_column_double(stmt, 3);
        float english = sqlite3_column_double(stmt, 4);
        float physics = sqlite3_column_double(stmt, 5);
        float chemistry = sqlite3_column_double(stmt, 6);
        float biology = sqlite3_column_double(stmt, 7);
        float total = sqlite3_column_double(stmt, 8);
        
        // 打印学生成绩
        printf("%-15s %-15s %-8.2f %-8.2f %-8.2f %-8.2f %-8.2f %-8.2f %-8.2f\n", 
               id, name, chinese, math, english, physics, chemistry, biology, total);
        
        // 更新统计信息
        stats.chinese_sum += chinese;
        stats.math_sum += math;
        stats.english_sum += english;
        stats.physics_sum += physics;
        stats.chemistry_sum += chemistry;
        stats.biology_sum += biology;
        stats.total_sum += total;
        
        // 更新最高分和最低分
        if (chinese > stats.max_chinese) stats.max_chinese = chinese;
        if (chinese < stats.min_chinese) stats.min_chinese = chinese;
        
        if (math > stats.max_math) stats.max_math = math;
        if (math < stats.min_math) stats.min_math = math;
        
        if (english > stats.max_english) stats.max_english = english;
        if (english < stats.min_english) stats.min_english = english;
        
        if (physics > stats.max_physics) stats.max_physics = physics;
        if (physics < stats.min_physics) stats.min_physics = physics;
        
        if (chemistry > stats.max_chemistry) stats.max_chemistry = chemistry;
        if (chemistry < stats.min_chemistry) stats.min_chemistry = chemistry;
        
        if (biology > stats.max_biology) stats.max_biology = biology;
        if (biology < stats.min_biology) stats.min_biology = biology;
        
        if (total > stats.max_total) stats.max_total = total;
        if (total < stats.min_total) stats.min_total = total;
    }
    
    sqlite3_finalize(stmt);
    
    if (stats.count == 0) {
        printf("该班级暂无成绩数据\n");
        return;
    }
    
    // 打印统计信息
    printf("\n=== %s班 成绩统计 ===\n", selected_class);
    printf("学生人数: %d\n", stats.count);
    
    printf("\n总分统计:\n");
    printf("最高分: %.2f\n", stats.max_total);
    printf("最低分: %.2f\n", stats.min_total);
    printf("平均分: %.2f\n", stats.total_sum / stats.count);
    
    printf("\n语文:\n");
    printf("最高分: %.2f\n", stats.max_chinese);
    printf("最低分: %.2f\n", stats.min_chinese);
    printf("平均分: %.2f\n", stats.chinese_sum / stats.count);
    
    printf("\n数学:\n");
    printf("最高分: %.2f\n", stats.max_math);
    printf("最低分: %.2f\n", stats.min_math);
    printf("平均分: %.2f\n", stats.math_sum / stats.count);
    
    printf("\n英语:\n");
    printf("最高分: %.2f\n", stats.max_english);
    printf("最低分: %.2f\n", stats.min_english);
    printf("平均分: %.2f\n", stats.english_sum / stats.count);
    
    printf("\n物理:\n");
    printf("最高分: %.2f\n", stats.max_physics);
    printf("最低分: %.2f\n", stats.min_physics);
    printf("平均分: %.2f\n", stats.physics_sum / stats.count);
    
    printf("\n化学:\n");
    printf("最高分: %.2f\n", stats.max_chemistry);
    printf("最低分: %.2f\n", stats.min_chemistry);
    printf("平均分: %.2f\n", stats.chemistry_sum / stats.count);
    
    printf("\n生物:\n");
    printf("最高分: %.2f\n", stats.max_biology);
    printf("最低分: %.2f\n", stats.min_biology);
    printf("平均分: %.2f\n", stats.biology_sum / stats.count);
}
